﻿using System.Collections.Generic;
using System.Linq;
using DAL.Common;
using DAL.POCO;
using Dapper;

namespace DAL.Repositories
{
    public class UserRepository
    {
        public static IEnumerable<User> GetUsers(int skip, int limit, out int totalItems)
        {
            string sql = @"SELECT * FROM
                              (
                                   SELECT ROW_NUMBER() OVER (ORDER BY u.login) as RowNumber, u.jmeno, u.prijmeni, u.login, u.admin, u.email
                                   FROM [User] u
                              ) as x
                              WHERE RowNumber BETWEEN @startRow AND @endRow";

            string sqlCount = "SELECT COUNT(login) FROM [User]";

            var users = MyConnection.Current.Query<User>(sql, new { startRow = skip, endRow = skip + limit - 1 });
            
            totalItems = MyConnection.Current.Query<int>(sqlCount).Single();
            return users;
        }

        public static User GetUser(string loginName)
        {
            string sql = "SELECT jmeno, prijmeni, login, admin, email FROM [User] WHERE login = @login";

            var user = MyConnection.Current.Query<User>(sql, new { login = loginName });
            return user.Single();
        }

        public static string GetUserPassword(string loginName)
        {
            string sql = "SELECT hashedPassword FROM [User] WHERE login = @login AND admin = 'TRUE'";

            var pw = MyConnection.Current.Query<string>(sql, new { login = loginName });
            return pw.SingleOrDefault();
        }

        public static void Insert(User user)
        {
            string sql = "INSERT INTO [User] (jmeno, prijmeni, login, admin, email, hashedPassword) VALUES (@jmeno, @prijmeni, @loginName, @isAdmin, @mail, @password)";

            MyConnection.Current.Execute(sql, new { jmeno = user.jmeno, prijmeni = user.prijmeni, loginName = user.login, isAdmin = user.admin, mail = user.email, password = user.hashedPassword});
        }

        public static void Update(User user)
        {
            string sql = "UPDATE [User] SET jmeno = @jmeno, prijmeni = @prijmeni, admin = @isAdmin, email = @mail WHERE login = @loginName";

            MyConnection.Current.Execute(sql, new { jmeno = user.jmeno, prijmeni = user.prijmeni, isAdmin = user.admin, mail = user.email, loginName = user.login });
        }

        public static void UpdatePassword(string loginName, string newPassword)
        {
            string sql = "UPDATE [User] SET hashedPassword = @password WHERE login = @login";

            MyConnection.Current.Execute(sql, new { password = newPassword, login = loginName });
        }

        public static void Delete(string loginName)
        {
            string sql = @"DELETE FROM [Score] WHERE userLogin = @login;
                           DELETE FROM [User] WHERE login = @login";

            MyConnection.Current.Execute(sql, new { login = loginName });
        }
    }
}
